package com.yihuier.yihuier.Mapper.RoomManager;

import com.yihuier.yihuier.Entity.RoomManager.Room;
import org.apache.ibatis.annotations.*;

import javax.websocket.server.PathParam;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

@Mapper
public interface RoomMapper {

    @Select("SELECT room_pic_url from bz_room WHERE room_id = #{room_id};")
    String selectMeetingRoomPicUrl(@Param("room_id") int room_id);

    @Results({
            @Result(property = "room_id" , column = "room_id"),
            @Result(property = "room_name" , column = "room_name"),
            @Result(property = "room_capacity" , column = "room_capacity"),
            @Result(property = "room_belong_company" , column = "room_belong_company"),
            @Result(property = "room_pic_url" , column = "room_pic_url"),
    })
    @Select("SELECT room_id,room_name,room_capacity,room_belong_company,room_pic_url from bz_room  WHERE  room_advertisement = 2 AND room_status = 1 AND room_parentstatus = 1;")
    List<Room> selectCarouselAdvertisementsRooms();

    @Select("SELECT room_pic_url from bz_room WHERE  room_id = #{location} AND room_status = 1;")
    String selectRoomPicByRoomId(@Param("location") Number location);

    /**
     * 根据会议室编号查询会议室名字
     * */
    @Select("SELECT room_name from bz_room WHERE  room_id = #{room_id} AND room_status = 1;")
    String selectRoomNameByRoomId(@Param("room_id") Long room_id);

    @Select("SELECT room_id FROM bz_room " +
            "WHERE room_parentstatus = 1 " +
            "AND room_capacity < #{numOfPeople} " +
            "AND room_capacity > #{numOfPeople}*0.8 " +
            "AND room_belong_company = " +
            "(SELECT department_belongs_company FROM bz_department WHERE department_id = " +
            "   (SELECT department_id FROM bz_department WHERE user_id = #{user_id})" +
            ");")
    int []selectAllRoomIdsCanUse(@Param("user_id")int user_id,@Param("numOfPeople") int numOfPeople);

    @Select("SELECT room_hardware FROM bz_room WHRER room_id = #{room_id};")
    String selectRoomsGear(@Param("room_id")int room_id);

    @Select("SELECT IFNULL(MAX(room_parentstatus),0) FROM bz_room WHERE room_id = #{room_id} AND room_parentstatus = 1 AND room_status = 1;")
    int selectRoomParentStatus(@Param("room_id") int room_id);

    @Results({
            @Result(property = "room_id",column = "room_id"),
            @Result(property = "room_name",column = "room_name"),
            @Result(property = "room_capacity",column = "room_capacity"),
            @Result(property = "room_lock",column = "room_lock"),
            @Result(property = "room_parentstatus",column = "room_parentstatus"),
            @Result(property = "room_status",column = "room_status"),
            @Result(property = "room_if_occupy",column = "room_if_occupy"),
            @Result(property = "room_belong_company",column = "room_belong_company"),
            @Result(property = "room_pic_url",column = "room_pic_url"),
            @Result(property = "room_advertisement",column = "room_advertisement"),
            @Result(property = "room_hardware",column = "room_hardware"),
        })
    @Select("SELECT room_id,room_name,room_capacity,room_lock,room_parentstatus,room_status,room_if_occupy,room_belong_company,room_pic_url,room_advertisement,room_hardware FROM bz_room WHERE room_id = #{room_id};")
    Room selectRoomById(@Param("room_id") int room_id);

    @Results({
            @Result(property = "room_id",column = "room_id"),
            @Result(property = "room_name",column = "room_name"),
            @Result(property = "room_capacity",column = "room_capacity"),
            @Result(property = "room_lock",column = "room_lock"),
            @Result(property = "room_parentstatus",column = "room_parentstatus"),
            @Result(property = "room_status",column = "room_status"),
            @Result(property = "room_if_occupy",column = "room_if_occupy"),
            @Result(property = "room_belong_company",column = "room_belong_company"),
            @Result(property = "room_pic_url",column = "room_pic_url"),
            @Result(property = "room_advertisement",column = "room_advertisement"),
            @Result(property = "room_hardware",column = "room_hardware"),
    })
    @Select("SELECT room_id,room_name,room_capacity,room_lock,room_parentstatus,room_status,room_if_occupy,room_belong_company,room_pic_url,room_advertisement,room_hardware FROM bz_room WHERE room_belong_company = #{company} " +
            "AND room_id NOT IN " +
            "(SELECT room_id FROM bz_meeting_room " +
            "WHERE meeting_time_start BETWEEN #{before} AND #{after} " +
            "AND meeting_time_end BETWEEN #{before} AND #{after} " +
            "AND room_id IN " +
            "(SELECT room_id FROM bz_room WHERE room_belong_company = #{company})" +
            ");")
    List<Room> selectAvailbleRooms(@Param("company") int company_id, @Param("before") Timestamp before, @Param("after") Timestamp after);

    /**
     *获取所在公司所有会议室
     * @param company_id 公司编号
     * */
    @Results({
            @Result(property = "room_id",column = "room_id"),
            @Result(property = "room_name",column = "room_name"),
            @Result(property = "room_capacity",column = "room_capacity"),
            @Result(property = "room_lock",column = "room_lock"),
            @Result(property = "room_parentstatus",column = "room_parentstatus"),
            @Result(property = "room_status",column = "room_status"),
            @Result(property = "room_if_occupy",column = "room_if_occupy"),
            @Result(property = "room_pic_url",column = "room_pic_url"),
            @Result(property = "room_advertisement",column = "room_advertisement"),
            @Result(property = "room_hardware",column = "room_hardware"),
    }
    )
    @Select("SELECT room_id,room_name,room_capacity,room_lock,room_parentstatus,room_status,room_if_occupy,room_pic_url,room_advertisement,room_hardware FROM bz_room WHERE room_belong_company = #{company_id} AND room_status = 1;")
    List<Room> selectRoomsByCompanyId(@Param("company_id")int company_id);

    @Select("SELECT room_id,room_name FROM bz_room WHERE room_belong_company = #{company_id};")
    List<Map<String,String>> selectRoomsByCompanyIdForOrder(@Param("company_id")int company_id);

    @Select("SELECT room_id,room_name from bz_room WHERE room_belong_company = #{company_id} AND  room_name like '%${content}%'")
    List<Map<String,Object>> selectRoomIdAndRoomNameByVague(@Param("content")String content,@Param("company_id") int company_id);

    @Update("UPDATE bz_room SET room_name = #{room_name} WHERE room_id = #{room_id} AND room_status = 1")
    int setRoomNameByRoomId(@Param("room_id")int room_id,@Param("room_name") String room_name);

    @Update("UPDATE bz_room SET room_capacity = #{room_capacity} WHERE room_id = #{room_id} AND room_status = 1")
    int setRoomCapacityByRoomId(@Param("room_id")String room_id,@Param("room_capacity") String room_capacity);

    @Update("UPDATE bz_room SET room_parentstatus = #{room_parentstatus} WHERE room_id = #{room_id} AND room_status = 1")
    int setRoomParentstatusByRoomId(@Param("room_id")String room_id,@Param("room_parentstatus") String room_parentstatus);

    @Update("UPDATE bz_room SET room_status = #{room_status} WHERE room_id = #{room_id}")
    int setRoomStatusByRoomId(@Param("room_id")String room_id,@Param("room_status") String room_status);

    @Insert("INSERT into bz_room(room_name,room_capacity,room_belong_company,room_hardware,room_add_time) VALUES(#{room_name},#{room_capacity},#{room_belong_company},#{room_hardware},#{room_add_time})")
    int insertRoom(@Param("room_name")String room_name,@Param("room_capacity") String room_capacity,@Param("room_belong_company")int room_belong_company,@Param("room_hardware") String room_hardware,@Param("room_add_time") String room_add_time);

    @Select("SELECT room_id from bz_room WHERE room_name = #{room_name} AND  room_capacity = #{room_capacity} AND room_belong_company = #{room_belong_company} AND room_hardware = #{room_hardware} AND room_add_time = #{room_add_time}")
    int selectRoomIdAfterAdd(@Param("room_name")String room_name,@Param("room_capacity") String room_capacity,@Param("room_belong_company")int room_belong_company,@Param("room_hardware") String room_hardware,@Param("room_add_time") String room_add_time);

    /**
     * 判断某个会议室是否属于某个公司
     * */
    @Select("SELECT room_id FROM bz_room WHERE room_id = #{location} AND room_belong_company = #{company_id};")
    String judgeRoomBelongsCompany(@Param("location")int location,@Param("company_id")int company_id);

    @Select("SELECT room_id,room_name,room_belong_company,room_pic_url,room_advertisement from bz_room WHERE (room_advertisement = 1 or room_advertisement = 2) AND room_status = 1;")
    List<Map<String,Object>> selcetAdvertisementRoom();

    @Select("SELECT Count(room_id) from bz_room WHERE room_advertisement = 2 AND room_status = 1;")
    int selcetAdvertisementRoomsCount();

    @Update("UPDATE bz_room SET room_advertisement = #{room_advertisement} WHERE room_id = #{room_id}")
    int updateRoomAdvertisementStatus(@Param("room_id") int room_id,@Param("room_advertisement") int room_advertisement);

    @Select("SELECT room_id FROM bz_room WHERE room_belong_company = #{company_id}")
    int[] getCompanyRoomIds(@PathParam("company_id") int company_id);

}
